package database;

import java.io.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.StringTokenizer;
import java.util.logging.Level;
import java.util.logging.Logger;
import logindata.User;

public class DatabaseInteraction {
    //general database information

    private static String url = "jdbc:mysql://ucfilespace.uc.edu:3306/boroffja";
    private static String userSQL = "boroffja";
    private static String passwordSQL = "teamramrod";

    public static Object[] getQueriesofType(int typeViewable) {
        ArrayList<String> queries = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("SELECT * FROM Queries");
            rs = pst.executeQuery();
            while (rs.next()) {
                queries.add(rs.getString(3));
            }
        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
        return queries.toArray();
    }
    /*
     * boolean postQuery(typeViewable, content, categories, createdby,
     * responses) input: int typeViewable - if 1 then student can see if 0 they
     * connot(unless creator), String content - the content in string format,
     * int categories - the category of the type of query, String createdBy -
     * The author of the query, String responses - replies in text of queries
     *
     */

    public static boolean postQuery(int typeViewable, String content, int categories, String createdBy, String responses) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("INSERT INTO Queries (typeViewable, category, createdby, content, responses) VALUES('"
                    + typeViewable + "','" + categories + "','" + createdBy + "','" + content + "','" + responses + "')");
            pst.executeUpdate();
        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
            r = false;
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
                r = true;
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        }
        return r;
    }
    /*
     * boolean postQuery(Query que) input: Query - the query to post to database
     *
     */

    public static boolean postQuery(Query que) {
        int typeViewable = Integer.parseInt(que.get("typeViewable").toString());
        int category = Integer.parseInt(que.get("category").toString());
        String content = que.get("content").toString();
        String response = que.get("responses").toString();
        String createdby = que.get("createdby").toString();
        return postQuery(typeViewable, content, category, createdby, response);
    }

    public static boolean editAnnouncment(int id, String column, String content) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection con = null;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            try {
                Statement st = con.createStatement();
                String sql = "UPDATE Announcements SET " + column + "='" + content + "' WHERE id='" + id + "'";
                int update = st.executeUpdate(sql);
                if (update == 1) {
                    r = true;
                } else {
                    return r;
                }
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return r;
    }

    /*
     * editQuery will update any column in the Query table in the database
     *
     * Usage: editQuery(34, "responses", "this is a response to a que")
     */
    public static boolean editQuery(int id, String column, String content) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection con = null;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            try {
                Statement st = con.createStatement();
                String sql = "UPDATE Queries SET " + column + "='" + content + "' WHERE id='" + id + "'";
                int update = st.executeUpdate(sql);
                if (update == 1) {
                    r = true;
                } else {
                    return r;
                }
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return r;
    }

    public static boolean deleteQuery(int id) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection con = null;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            try {
                Statement st = con.createStatement();
                String sql = "DELETE FROM Queries WHERE id='" + id + "'";
                int delete = st.executeUpdate(sql);
                if (delete == 1) {
                    r = true;
                } else {
                    return r;
                }
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return r;
    }

    public static Query[] getAllQueries() {
        ArrayList<Query> queries = new ArrayList<Query>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int id;
        int typeViewable;
        String content;
        int category;
        String createdBy;
        String responses;
        Query temp;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("SELECT * FROM Queries");
            rs = pst.executeQuery();
            while (rs.next()) {
                id = rs.getInt("id");
                typeViewable = rs.getInt("typeViewable");
                category = rs.getInt("category");
                createdBy = rs.getString("createdby");
                content = rs.getString("content");
                responses = rs.getString("responses");
                temp = new Query(id, typeViewable, category, createdBy,
                        content, responses);
                queries.add(temp);
            }

        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
        return queries.toArray(new Query[queries.size()]);
    }
    /*
     * public static boolean Announcement(String createdby, String announcement)
     * - post announcement to database given the created by and the announcement
     * returns true if no errors occured, false otherwise
     */

    public static boolean postAnnouncement(String createdby, String announcement) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("INSERT INTO Announcements (createdby, content) VALUES('"
                    + createdby + "','" + announcement + "')");
            pst.executeUpdate();
        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
            r = false;
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
                r = true;
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        }
        return r;
    }

    public static boolean postAnnouncement(Announcement announce) {
        String createdby = announce.getCreateBy();
        String content = announce.getContent();
        return postAnnouncement(createdby, content);
    }

    public static Announcement[] getAllAnnouncements() {
        ArrayList<Announcement> announce = new ArrayList<Announcement>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int id;
        String content;
        String createdBy;
        Announcement temp;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("SELECT * FROM Announcements");
            rs = pst.executeQuery();
            while (rs.next()) {
                id = rs.getInt("id");
                createdBy = rs.getString("createdby");
                content = rs.getString("content");
                temp = new Announcement(id, createdBy, content);
                announce.add(temp);
            }

        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
        return announce.toArray(new Announcement[announce.size()]);
    }

    public static boolean deleteAnnouncement(int id) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection con = null;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            try {
                Statement st = con.createStatement();
                String sql = "DELETE FROM Announcements WHERE id='" + id + "'";
                int delete = st.executeUpdate(sql);
                if (delete == 1) {
                    r = true;
                } else {
                    return r;
                }
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return r;
    }

    public static boolean postAvailability(Availability ava) {
        int[] toPost0 = ava.getDayIntForm(0);
        int[] toPost1 = ava.getDayIntForm(1);
        int[] toPost2 = ava.getDayIntForm(2);
        int[] toPost3 = ava.getDayIntForm(3);
        int[] toPost4 = ava.getDayIntForm(4);
        int[] toPost5 = ava.getDayIntForm(5);
        int[] toPost6 = ava.getDayIntForm(6);
        String zero, one, two, three, four, five, six;
        zero = makeString(toPost0);
        one = makeString(toPost1);
        two = makeString(toPost2);
        three = makeString(toPost3);
        four = makeString(toPost4);
        five = makeString(toPost5);
        six = makeString(toPost6);
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String query;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("SELECT * FROM Availability");
            rs = pst.executeQuery();
            query = "INSERT INTO Availability (username, sunday, monday, tuesday, wednesday, thursday, friday, saturday) VALUES('"
                    + ava.getUsername() + "','" + zero + "','" + one + "','" + two + "','" + three + "','" + four + "','" + five + "','" + six + "')";
            while (rs.next()) {
                if (rs.getString("username").equals(ava.getUsername())) {
                    query = "UPDATE Availability SET sunday='" + zero + "', monday='" + one + "', tuesday='" + two + "', wednesday='" + three + "', thursday='" + four + "', friday='" + five + "', saturday='" + six + "' WHERE username='" + ava.getUsername() + "'";
                    break;
                }
            }
            pst = con.prepareStatement(query);
            pst.executeUpdate();
        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
            r = false;
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
                r = true;
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        }
        return r;
    }

    public static String makeString(int[] ints) {
        String ans = "";
        for (int i = 0; i < ints.length - 1; i++) {
            ans = ans + ints[i] + ",";
        }
        ans = ans + ints[ints.length - 1];
        return ans;

    }

    public static Availability getAvailability(String username) {
        Availability ans = new Availability(username);
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String[] day = new String[7];
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("SELECT * FROM Availability");
            rs = pst.executeQuery();
            while (rs.next()) {
                if (rs.getString("username").equals(username)) {
                    day[0] = rs.getString("sunday");
                    day[1] = rs.getString("monday");
                    day[2] = rs.getString("tuesday");
                    day[3] = rs.getString("wednesday");
                    day[4] = rs.getString("thursday");
                    day[5] = rs.getString("friday");
                    day[6] = rs.getString("saturday");
                    break;
                }
            }
            for (int i = 0; i < 7; i++) {
                if (day[i] == null) {
                    day[i] = "0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0";
                }
            }
            boolean[] temp;
            for (int i = 0; i < day.length; i++) {
                temp = makeArray(day[i]);
                try {
                    ans.setDay(i, temp);
                } catch (Exception ex) {
                    Logger.getLogger(DatabaseInteraction.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
        return ans;
    }

    public static boolean[] makeArray(String text) {
        boolean[] ans;
        int spot;
        StringTokenizer token = new StringTokenizer(text, ",");
        ans = new boolean[token.countTokens()];
        int i = 0;
        while (token.hasMoreTokens()) {
            spot = Integer.parseInt(token.nextToken());
            if (spot == 1) {
                ans[i] = true;
            } else {
                ans[i] = false;
            }
            i++;
        }
        return ans;
    }

    public static boolean postUser(String currentUsername, String username, String password, int type) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("UPDATE UserData SET idUserName='" + username + "', password='" + password + "', type='" + type + "' WHERE idUserName='" + currentUsername + "'");
            pst.executeUpdate();
        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
            r = false;
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
                r = true;
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        }
        return r;
    }

    public static User[] getAllUsers() {
        ArrayList<User> users = new ArrayList<User>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int id;
        String username;
        String password;
        int type;
        User temp;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("SELECT * FROM UserData");
            rs = pst.executeQuery();
            while (rs.next()) {
                username = rs.getString("idUserName");
                password = rs.getString("password");
                type = rs.getInt("type");
                temp = new User(username, password, type);
                users.add(temp);
            }

        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
        return users.toArray(new User[users.size()]);
    }

    public static User[] getUsers(int typeToGet) {
        ArrayList<User> users = new ArrayList<User>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int id;
        String username;
        String password;
        int type;
        User temp;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("SELECT * FROM UserData");
            rs = pst.executeQuery();
            while (rs.next()) {
                type = rs.getInt("type");
                if (type == typeToGet) {
                    username = rs.getString("idUserName");
                    password = rs.getString("password");
                    temp = new User(username, password, type);
                    users.add(temp);
                }
            }

        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
        return users.toArray(new User[users.size()]);
    }

    public static boolean postForm(String username, String filename, File formdata) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("INSERT INTO Forms (username, formdata, filename, reviewed) VALUES('"
                    + username + "','" + formdata + "','" + filename + "','0')");
            pst.executeUpdate();
        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
            r = false;
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
                r = true;
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        }
        return r;
    }

    public static boolean deleteForm(String username, String filename) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection con = null;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            try {
                Statement st = con.createStatement();
                String sql = "DELETE FROM Forms WHERE username='" + username + "'" + " AND filename='" + filename + "'";
                int delete = st.executeUpdate(sql);
                if (delete == 1) {
                    r = true;
                } else {
                    return r;
                }
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return r;
    }

    public static String[] getAllUnreviewedForms(String username) {
        ArrayList<String> filenames = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int id;
        String filename;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("SELECT * FROM Forms WHERE reviewed=0 AND username='" + username + "'");
            rs = pst.executeQuery();
            while (rs.next()) {
                filename = rs.getString("filename");
                filenames.add(filename);
            }

        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
        return filenames.toArray(new String[filenames.size()]);
    }

    public static String[][] getUserForms(String username) {
        String[][] formStatus = new String[100][100];
        int counter = 0;
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pst = con.prepareStatement("SELECT * FROM Forms WHERE username='" + username + "'");
            rs = pst.executeQuery();
            while (rs.next()) {
                if (rs.getString("filename") != null) {
                    formStatus[counter][0] = rs.getString("filename");
                    formStatus[counter][1] = rs.getString("reviewed");
                    counter++;
                }
            }
        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
        return formStatus;
    }

    public static File getSelectedForm(String username, String filename) throws FileNotFoundException, IOException {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        File file = null;
        String fname = filename;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            pstmt = con.prepareStatement("SELECT * FROM Forms WHERE filename='" + fname + "'");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Blob blob = rs.getBlob("formdata");
                byte[] array = blob.getBytes(1, (int) blob.length());
                file = File.createTempFile("test", ".pdf", new File("."));
                FileOutputStream out = new FileOutputStream(file);
                out.write(array);
                out.close();
                break;
            }

        } catch (SQLException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
        } catch (IOException e) {
            javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured reading form", "Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
            Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
            lgr.log(Level.SEVERE, e.getMessage(), e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
        return file = new File(file.getName());
    }

    public static boolean updateFormReviewStatus(String username, String filename, int status) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection con = null;
        boolean r = false;
        try {
            con = DriverManager.getConnection(url, userSQL, passwordSQL);
            try {
                Statement st = con.createStatement();
                String sql = "UPDATE Forms SET reviewed='" + status + "' WHERE username='" + username + "' AND filename='" + filename + "'";
                int delete = st.executeUpdate(sql);
                if (delete == 1) {
                    r = true;
                } else {
                    return r;
                }
            } catch (SQLException e) {
                javax.swing.JOptionPane.showMessageDialog(null, "An Error Occured Connecting to database", "Connection Error", javax.swing.JOptionPane.ERROR_MESSAGE, null);
                Logger lgr = Logger.getLogger(DatabaseInteraction.class.getName());
                lgr.log(Level.SEVERE, e.getMessage(), e);
                r = false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return r;
    }
}
